<?php
class Dpcmodel extends CI_Model {

	function __construct()
	{
		parent::__construct();
	}

	public function init(){
		$dbHandle = $this->load->database('dpchandle', TRUE);
		if($dbHandle == ''){
			error_log('can not create db handle','dpchandle');
			echo (print_r($dbHandle,true));
		}
		return $dbHandle;
	}
	public function rateYourEndure($storeId, $ratingStar, $ipAddr){
		$dbHandle = $this->init();
		if($ratingStar!=0){
			$query = "select * from dpc_ratings_tbl where store_id=$storeId and ip_address='$ipAddr'";
			error_log("insert ratings query ".$query);
			$result = mysql_query ($query);
			if(mysql_num_rows ($result) <= 0){
				$query = "INSERT INTO dpc_ratings_tbl(rating_star, store_id, ip_address) values($ratingStar, $storeId, '$ipAddr')";
				error_log("insert ratings query ".$query);
				$result = mysql_query ($query);
			}
		}
		$query = "select count(*) as count, sum(rating_star) as ratings_total from dpc_ratings_tbl where store_id=$storeId";
		error_log("get ratings query ".$query);
		$result = mysql_query ($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}
	public function getStoreDetailsById($storeId){
		$dbHandle = $this->init();
		$query = "select * from dpc_store_tbl cst, dpc_category_store_tbl ccst where cst.store_id=ccst.store_id and cst.store_id='$storeId' and cst.store_status='Yes'";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
	public function getParentCategories(){
		$dbHandle = $this->init();
		$query = "select * from dpc_parent_cat_tbl where parent_status='Yes' order by parent_name";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getParentCatByUrl($url){
		$dbHandle = $this->init();
		$query = "select * from dpc_parent_cat_tbl where parent_url='$url' and parent_status='Yes' order by parent_name";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getCatByPId($catId){
		$dbHandle = $this->init();
		$query = "select * from dpc_category_tbl where category_parent='$catId' and category_status='Yes' order by category_name";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getOtherParent($catId){
		$dbHandle = $this->init();
		$query = "select * from dpc_parent_cat_tbl where parent_id!='$catId' and parent_status='Yes' order by parent_name";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getCatRandomBanner($catId){
		$dbHandle = $this->init();
		$query = "select * from dpc_banners_tbl where banner_category='$catId' and banner_status='Yes' order by rand() limit 0, 1";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getParentCatCoupons($catId, $start, $limit){
		$dbHandle = $this->init();
		if($start=='' && $limit==''){
			$query = "select dst.*, dc.*, datediff(date(dc.coupon_expiredate),curdate()) as diff from dpc_category_tbl dct, dpc_store_tbl dst, dpc_category_store_tbl dcst, dpc_coupons_tbl dc, dpc_store_coupon_tbl dsct where dct.category_parent='$catId' and dct.category_id=dcst.category_id and dst.store_id=dcst.store_id and dst.store_id=dsct.store_id and dc.coupon_id=dsct.coupon_id and dct.category_id=dc.coupon_category and dc.coupon_expiredate>=CURDATE() and dc.coupon_type!='offer' and dc.coupon_status='Yes' group by dc.coupon_category order by dc.coupon_creationdate desc";
		}else{
			$query = "select dst.*, dc.*, datediff(date(dc.coupon_expiredate),curdate()) as diff from dpc_category_tbl dct, dpc_store_tbl dst, dpc_category_store_tbl dcst, dpc_coupons_tbl dc, dpc_store_coupon_tbl dsct where dct.category_parent='$catId' and dct.category_id=dcst.category_id and dst.store_id=dcst.store_id and dst.store_id=dsct.store_id and dc.coupon_id=dsct.coupon_id and dct.category_id=dc.coupon_category and dc.coupon_expiredate>=CURDATE() and dc.coupon_type!='offer' and dc.coupon_status='Yes' group by dc.coupon_category order by dc.coupon_creationdate desc limit $start, $limit";
		}
		error_log('fgfgfgfgfgfgfgfg     '.$query);
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getParentCatOffers($catId, $count){
		$dbHandle = $this->init();
		$query = "select dst.*, dc.*, datediff(date(dc.coupon_expiredate),curdate()) as diff from dpc_category_tbl dct, dpc_store_tbl dst, dpc_category_store_tbl dcst, dpc_coupons_tbl dc, dpc_store_coupon_tbl dsct where dct.category_parent='$catId' and dct.category_id=dcst.category_id and dst.store_id=dcst.store_id and dst.store_id=dsct.store_id and dc.coupon_id=dsct.coupon_id and dc.coupon_expiredate>=CURDATE() and dc.coupon_type='offer' and dc.coupon_status='Yes' group by dst.store_id order by dc.coupon_creationdate desc limit 0, $count";
		//error_log('skdjccdcjdsjc     '.$query);
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getTopDeals(){
		$dbHandle = $this->init();
        $date=date('Y-m-d');
		$query = "select * from dpc_deals_tbl where deal_status='Yes' and deal_expiredate>='$date' order by deal_creationdate desc";
		$result = mysql_query($query);
		$topDealsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$topDealsArray[$i] = $row;
			$i++;
		}
		return $topDealsArray;
}

	public function getAllCategories(){
		$dbHandle = $this->init();
		$query = "select c.*, cs.store_count, p.* from dpc_category_tbl c, (select count(store_id) as store_count, category_id from dpc_category_store_tbl group by category_id) cs, dpc_parent_cat_tbl p where c.category_id=cs.category_id and c.category_parent=p.parent_id group by category_id order by c.category_name asc";
	
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	public function getTypeCoupons($type, $storeUrl="Home", $limit='10'){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$ExpDate=date("Y-m-d",strtotime("-1 Months"));
		$query='';
		$query .= "select cpn.*, str.*, csct.*, datediff(date(cpn.coupon_expiredate),curdate()) as diff from dpc_coupons_tbl cpn, dpc_store_tbl str, dpc_store_coupon_tbl csct where ";
		if($type=='TopCpn' || $type=='FeaturedCpn' || $type=='Offer'){
			$query .= " coupon_type='$type' ";
		}else{
			$query .= " cpn.coupon_type!='Offer' ";
		}
		if($type=='TopCpn' || $type=='FeaturedCpn' || $type=='Cpn' || $type=='Offer'){
			$query .= " and cpn.coupon_expiredate>='$date' ";
		}else if ($type=='ExpiredCpn'){
			$query .= " and cpn.coupon_expiredate<'$date' and cpn.coupon_expiredate>'$ExpDate' ";
		}
		if ($storeUrl!="Home"){
			$query .= " and str.store_url='$storeUrl' ";
		}
		$query .= " and cpn.coupon_id=csct.coupon_id and csct.store_id=str.store_id and cpn.coupon_status='Yes' and str.store_status='Yes' order by cpn.coupon_creationdate desc limit $limit";
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
}

    public function getStoreDetails($storeUrl){
		$dbHandle = $this->init();
		$query = "select * from dpc_store_tbl s, dpc_category_store_tbl cs where s.store_id=cs.store_id and s.store_url='$storeUrl' and s.store_status='Yes'";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
    }
    
    public function getStoreCoupons($storeId){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select c.*, sc.*, s.*, datediff(date(c.coupon_expiredate),curdate()) as diff from dpc_coupons_tbl c, dpc_store_tbl s, dpc_store_coupon_tbl sc where c.coupon_id=sc.coupon_id and s.store_id=sc.store_id and sc.store_id='$storeId' and c.coupon_expiredate >=  '$date' and c.coupon_status='Yes' order by c.coupon_creationdate desc";
		error_log("get store coupons query is ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getStoreCouponsCMS($storeId){
                $dbHandle = $this->init();
                $date=date('Y-m-d');
                $query = "select c.*, sc.*, s.*, datediff(date(c.coupon_expiredate),curdate()) as diff from dpc_coupons_tbl c, dpc_store_tbl s, dpc_store_coupon_tbl sc where c.coupon_id=sc.coupon_id and s.store_id=sc.store_id and sc.store_id='$storeId' and c.coupon_expiredate >=  '$date' and c.coupon_status in('Yes','Process') order by c.coupon_creationdate desc";
                error_log("get store coupons query is ".$query);
                $result = mysql_query($query);
                $couponsArray = array();
                $i=0;
                while($row = mysql_fetch_assoc($result)){
                        $couponsArray[$i] = $row;
                        $i++;
                }
                return $couponsArray;
        }

	public function getStoreBanners($store_id){
		$dbHandle = $this->init();
		$query = "select * from dpc_banners_tbl where banner_store_id=$store_id and banner_status='Yes' order by banner_creationdate desc limit 1";
		$result = mysql_query($query);
		if(mysql_num_rows ($result) <= 0)
		{
			$query = "select * from dpc_banners_tbl where banner_store_id=0 and banner_status='Yes' order by rand() limit 1";
			$result = mysql_query ($query);
		}
		$bannersArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$bannersArray[$i] = $row;
			$i++;
		}
		return $bannersArray;
	}
	
	public function getRandomBanners(){
		$dbHandle = $this->init();
		$query = "select * from dpc_banners_tbl where banner_status='Yes' order by rand() limit 1";
		$result = mysql_query($query);
		$bannersArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$bannersArray[$i] = $row;
			$i++;
		}
		return $bannersArray;
	}
	
	public function getRelatedCategories($storeId, $catId){
		$dbHandle = $this->init();
		$query = "select * from dpc_category_store_tbl dcs, dpc_category_tbl dct, dpc_parent_cat_tbl dpc where dcs.store_id='$storeId' and dcs.category_id=dct.category_id and dct.category_parent=dpc.parent_id and dct.category_id!='$catId' and dct.category_status='Yes' order by dct.category_name";
		error_log("get nitesh cat is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
	
	public function getRelatedStores($storeId, $catId){
		$dbHandle = $this->init();
		$query = "select * from dpc_store_tbl where store_id!='$storeId' and store_cat_id='$catId' and store_status='Yes' order by store_name";
		error_log("get nitesh store is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
	
	public function getStoreReviews($storeId){
		$dbHandle = $this->init();
		$query = "select * from dpc_store_reviews_tbl where reviews_store_id='$storeId' and reviews_status='Yes' order by reviews_creationdate desc";
		error_log("get nitesh store is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
	
	public function getAlphaStores($alpha){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
	if($alpha=='0-9'){
			$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
			FROM dpc_coupons_tbl c, dpc_store_tbl s, dpc_store_coupon_tbl sc
			WHERE c.coupon_id = sc.coupon_id
			AND s.store_id = sc.store_id
			AND c.coupon_expiredate >=  '$date'
			AND c.coupon_status='Yes'
			AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '0' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '1' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '2' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '3' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '4' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '5' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '6' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '7' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '8' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '9')
			GROUP BY s.store_id union
			SELECT s.*, 0 AS noOfCoupons
			FROM dpc_store_tbl s, dpc_store_coupon_tbl sc
			WHERE s.store_id != sc.store_id
			AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '0' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '1' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '2' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '3' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '4' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '5' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '6' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '7' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '8' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '9')
			GROUP BY s.store_id) as temp
			group by temp.store_id ORDER BY temp.store_order ASC";
		} elseif($alpha!=''){
			$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
FROM dpc_coupons_tbl c, dpc_store_tbl s, dpc_store_coupon_tbl sc
WHERE c.coupon_id = sc.coupon_id
AND s.store_id = sc.store_id
AND c.coupon_expiredate >=  '$date'
AND c.coupon_status='Yes'
AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '$alpha')
GROUP BY s.store_id union
SELECT s.*, 0 AS noOfCoupons
FROM dpc_store_tbl s, dpc_store_coupon_tbl sc
WHERE s.store_id != sc.store_id
AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '$alpha')
GROUP BY s.store_id) as temp where temp.store_status='Yes'
group by temp.store_id ORDER BY temp.store_order ASC";
		} else {
			$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
FROM dpc_coupons_tbl c, dpc_store_tbl s, dpc_store_coupon_tbl sc
WHERE c.coupon_id = sc.coupon_id
AND s.store_id = sc.store_id
AND c.coupon_expiredate >=  '$date'
AND c.coupon_status='Yes'
GROUP BY s.store_id union
SELECT s.*, 0 AS noOfCoupons
FROM dpc_store_tbl s, dpc_store_coupon_tbl sc
WHERE s.store_id != sc.store_id
GROUP BY s.store_id) as temp where temp.store_status='Yes'
group by temp.store_id ORDER BY temp.store_order ASC";
		}
		error_log("get ALL store query is ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getCouponStores($coupon_id){
		$dbHandle = $this->init();
		$query = "SELECT store_id FROM dpc_store_coupon_tbl where coupon_id='$coupon_id'";
		$result = mysql_query($query);
		$couponStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			array_push($couponStoresArray, $row['store_id']);
			$i++;
		}
		return $couponStoresArray;
	}
	
	public function getPopStoreList(){
		$dbHandle = $this->init();
		$query = "select * from dpc_store_tbl where store_order='0' and store_status='Yes' order by store_name";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	
	public function getSubCatByUrl($catUrl){
		$dbHandle = $this->init();
		$query = "select * from dpc_category_tbl where category_url='$catUrl' and category_status='Yes'";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	
	public function getCategoryStores($categoryId, $limit='16'){
		$dbHandle = $this->init();
		if($categoryId=='popular'){
			$query = "SELECT * FROM dpc_store_tbl ORDER BY store_order ASC limit $limit";
		}else{
			$query = "SELECT distinct cst.* FROM dpc_store_tbl cst, dpc_category_store_tbl ccst where cst.store_id=ccst.store_id and ccst.category_id='$categoryId' ORDER BY store_order ASC";
		}
		$result = mysql_query($query);
		$alphaStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$alphaStoresArray[$i] = $row;
			$i++;
		}
		return $alphaStoresArray;
	}
	
	public function getTopCategoryCoupons($category_id, $store_id, $limit){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select c.*, s.*, datediff(date(c.coupon_expiredate),curdate()) as diff from dpc_coupons_tbl c, dpc_store_tbl s, dpc_category_store_tbl cs, dpc_store_coupon_tbl sc where cs.store_id=s.store_id and c.coupon_type!='Offer' and s.store_id=sc.store_id and c.coupon_id=sc.coupon_id and sc.store_id!=$store_id and s.store_type='Money'";
		if($category_id!=0){
			$query.=" and cs.category_id=$category_id and c.coupon_category=$category_id ";
		}else{
			$query.=" and cs.category_id in(select cs.category_id from dpc_category_store_tbl cs where cs.store_id=$store_id)";
		}
		$query.=" and c.coupon_status='Yes' and c.coupon_expiredate>='$date' order by rand() limit $limit";
		error_log("getTopCategoryCoupons query is ".$query);
		$result = mysql_query($query);
		$catCouponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$catCouponsArray[$i] = $row;
			$i++;
		}
		return $catCouponsArray;
	}
	
	public function getTopCategoryOffers($category_id, $store_id, $limit){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select c.*, s.*, datediff(date(c.coupon_expiredate),curdate()) as diff from dpc_coupons_tbl c, dpc_store_tbl s, dpc_category_store_tbl cs, dpc_store_coupon_tbl sc where cs.store_id=s.store_id and c.coupon_type='Offer' and s.store_id=sc.store_id and c.coupon_id=sc.coupon_id and sc.store_id!=$store_id and s.store_type='Money'";
		if($category_id!=0){
			$query.=" and cs.category_id=$category_id and c.coupon_category=$category_id ";
		}else{
			$query.=" and cs.category_id in(select cs.category_id from dpc_category_store_tbl cs where cs.store_id=$store_id)";
		}
		$query.=" and c.coupon_status='Yes' and c.coupon_expiredate>='$date' order by rand() limit $limit";
		error_log("getTopCategoryCoupons query is ".$query);
		$result = mysql_query($query);
		$catCouponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$catCouponsArray[$i] = $row;
			$i++;
		}
		return $catCouponsArray;
	}
	
	public function getAffiliateURLByCouponId($coupon_id, $store_id){
		$dbHandle = $this->init();
		//$query = "SELECT coupon_affilateurl from dpc_coupons_tbl where coupon_id='$coupon_id'";
		$query="SELECT cct.coupon_affilateurl, cst.store_redirecturl, cst.store_affilateurl, cst.store_utmsource from dpc_coupons_tbl cct,dpc_store_tbl cst where cct.coupon_id='$coupon_id' And cst.store_id='$store_id'";
		$result = $dbHandle->query($query);
		$affArray = array();
		foreach($result->result() as $row){
	
			array_push($affArray,array(
					'coupon_affilateurl'=>$row->coupon_affilateurl, 'store_redirecturl'=>$row->store_redirecturl,'store_affilateurl'=>$row->store_affilateurl,'store_utmsource'=>$row->store_utmsource,
			));
		}
			
		if($affArray[0]['store_affilateurl']==''){
			$query = "SELECT store_affilateurl from dpc_store_tbl where store_id='$store_id'";
			$result = $dbHandle->query($query);
			$affArray = array();
			foreach($result->result() as $row){
				array_push($affArray,array(
						'store_affilateurl'=>$row->store_affilateurl,
				));
			}
		}
		return $affArray;
	}
	
	public function getAffiliateURLByDealId($coupon_id, $store_id){
		$dbHandle = $this->init();
		//$query = "SELECT coupon_affilateurl from dpc_coupons_tbl where coupon_id='$coupon_id'";
		$query="SELECT cct.deal_affilateurl, cst.store_redirecturl, cst.store_affilateurl, cst.store_utmsource from dpc_deals_tbl cct,dpc_store_tbl cst where cct.deal_id='$coupon_id' And cst.store_id='$store_id'";
		$result = $dbHandle->query($query);
		$affArray = array();
		foreach($result->result() as $row){
	
			array_push($affArray,array(
					'deal_affilateurl'=>$row->deal_affilateurl, 'store_redirecturl'=>$row->store_redirecturl,'store_affilateurl'=>$row->store_affilateurl,'store_utmsource'=>$row->store_utmsource,
			));
		}
			
		if($affArray[0]['store_affilateurl']==''){
			$query = "SELECT store_affilateurl from dpc_store_tbl where store_id='$store_id'";
			$result = $dbHandle->query($query);
			$affArray = array();
			foreach($result->result() as $row){
				array_push($affArray,array(
						'store_affilateurl'=>$row->store_affilateurl,
				));
			}
		}
		return $affArray;
	}
	
	public function searchStore($tag){
		$dbHandle = $this->init();
		$searchStoresArray = array();
		$query="SELECT group_concat(distinct store_id) as store_id FROM dpc_tags_tbl WHERE store_tag LIKE '%$tag%' GROUP BY store_id";
		//error_log("query is ".$query);
		$result = $dbHandle->query($query);
		foreach($result->result() as $row){
			$ids=$row->store_id;
		}
		if (isset($ids) && $ids!=""){
			$query="SELECT * FROM dpc_store_tbl WHERE store_status='Yes' AND store_id IN($ids)";
			$result = mysql_query($query);
			$i=0;
			while($row = mysql_fetch_assoc($result)){
				$searchStoresArray[$i] = $row;
				$i++;
			}
		}
		return $searchStoresArray;
	}
	
	public function subscribeEmailId($emailId){
		$dbHandle = $this->init();
		$subscribeId=-1;
		if($emailId['storeid']!='')
		{
			$storeId = $emailId['storeid'];
			$storeName = $emailId['storename'];
		}else{
			$storeId = '-1';
			$storeName = 'Home';
		}
		$queryCmd = "insert into dpc_subscribe_tbl (subscribe_emailid, subscribe_name, subscribe_store_id, subscribe_store_name, subscribe_status) values('".addslashes($emailId['email'])."', '".addslashes($emailId['name'])."', '".addslashes($storeId)."', '".addslashes($storeName)."', 'Yes')";
		$query = $dbHandle->query($queryCmd);
		//error_log("queryCmd is ", $queryCmd);
		$subscribeId=$dbHandle->insert_id();
		return $subscribeId;
	}
	
	public function getRandomReviews(){
		$dbHandle = $this->init();
		$query = "SELECT * FROM `dpc_store_reviews_tbl` where reviews_status='Yes' order by reviews_creationdate desc limit 0, 5";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}
	
	public function insertContact($post){
		$dbHandle = $this->init();
		$query = "INSERT INTO `dpc_contactus_tbl`(`contact_name`, `contact_subject`, `contact_message`, `contact_email`) VALUES ('".$post['name']."', '".$post['subject']."', '".$post['message']."', '".$post['email']."')";
		$result = mysql_query($query);
	}
	
	public function getAllStore(){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
		FROM dpc_coupons_tbl c, dpc_store_tbl s, dpc_store_coupon_tbl sc
		WHERE c.coupon_id = sc.coupon_id
		AND s.store_id = sc.store_id AND s.store_status='Yes'
		AND c.coupon_expiredate >=  '$date'
		AND c.coupon_status='Yes'
		GROUP BY s.store_id union
		SELECT s.*, 0 AS noOfCoupons
		FROM dpc_store_tbl s, dpc_store_coupon_tbl sc
		WHERE s.store_id != sc.store_id AND s.store_status='Yes'
		GROUP BY s.store_id) as temp
		group by temp.store_id ORDER BY temp.store_name ASC";;
		error_log("get ALL store query is ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getAllStores(){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select * from dpc_store_tbl where store_status='Yes' order by store_name";
		error_log("get ALL store query is ".$query);
				$result = mysql_query($query);
				$couponsArray = array();
				$i=0;
				while($row = mysql_fetch_assoc($result)){
				$couponsArray[$i] = $row;
				$i++;
				}
				return $couponsArray;
	}
	
	public function getAllHomeBanner()
	{
		$dbHandle = $this->init();
		$query= "Select * from dpc_homebanner_tbl where homebanner_status='Yes' ";
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

}
